from django.http import HttpResponse, JsonResponse
from django.views.decorators.http import require_http_methods
import base64, json
from .confs.conf import get_mirror_conn
from openpyxl import Workbook
from io import BytesIO
from datetime import datetime,timedelta

def_response_o = {
    "code": 0,
    "result": "",
    "message": "",
    "type": 'success',
  }


counts = """SELECT COUNT(*) """
select = '''
SELECT hd.hr, lt.tm, lt.evname, lt.num ,lt.money FROM 
    ( SELECT '00' AS hr 
        UNION SELECT '01'
        UNION SELECT '02' 
        UNION SELECT '03' 
        UNION SELECT '04' 
        UNION SELECT '05' 
        UNION SELECT '06' 
        UNION SELECT '07' 
        UNION SELECT '08' 
        UNION SELECT '09' 
        UNION SELECT '10' 
        UNION SELECT '11' 
        UNION SELECT '12' 
        UNION SELECT '13' 
        UNION SELECT '14' 
        UNION SELECT '15' 
        UNION SELECT '16' 
        UNION SELECT '17' 
        UNION SELECT '18' 
        UNION SELECT '19' 
        UNION SELECT '20' 
        UNION SELECT '21' 
        UNION SELECT '22' 
        UNION SELECT '23') AS hd
				LEFT JOIN ( SELECT SUBSTRING(tm, 12, 2) AS hr, tm,evname, count(tl) AS 'num',sum(money) as 'money' FROM 
				( SELECT uid, SUBSTRING(DATE_ADD(`time`, INTERVAL 8 HOUR), 1, 13) AS tm, COUNT(*) AS tl,
				sum(SUBSTRING_INDEX(SUBSTRING_INDEX(content,",uemail:",1),'total:',-1)) as money, evname FROM `gather_record` 
WHERE evname in ('ev_name') AND `time`>= 'star_time' AND `time`< 'end_time' AND `host`='site_str'
AND sip not in (select ip from `dlz_exclude_ip`)
GROUP BY tm, uid
) AS tb GROUP BY tm ORDER BY hr ) AS lt ON (hd.hr=lt.hr)
'''


select_checkout = '''
SELECT hd.hr, lt.tm, 'checkout_out' as evname, lt.num ,lt.money FROM 
    ( SELECT '00' AS hr 
        UNION SELECT '01'
        UNION SELECT '02' 
        UNION SELECT '03' 
        UNION SELECT '04' 
        UNION SELECT '05' 
        UNION SELECT '06' 
        UNION SELECT '07' 
        UNION SELECT '08' 
        UNION SELECT '09' 
        UNION SELECT '10' 
        UNION SELECT '11' 
        UNION SELECT '12' 
        UNION SELECT '13' 
        UNION SELECT '14' 
        UNION SELECT '15' 
        UNION SELECT '16' 
        UNION SELECT '17' 
        UNION SELECT '18' 
        UNION SELECT '19' 
        UNION SELECT '20' 
        UNION SELECT '21' 
        UNION SELECT '22' 
        UNION SELECT '23') AS hd
				LEFT JOIN ( SELECT SUBSTRING(tm, 12, 2) AS hr, tm,evname, count(tl) AS 'num',sum(money) as 'money' FROM 
				( SELECT uid, SUBSTRING(DATE_ADD(`time`, INTERVAL 8 HOUR), 1, 13) AS tm, COUNT(*) AS tl,
				sum(SUBSTRING_INDEX(SUBSTRING_INDEX(content,",uemail:",1),'total:',-1)) as money, evname FROM `gather_record` 
WHERE evname in ('ev_name') AND `time`>= 'star_time' AND `time`< 'end_time' AND `host`='site_str' AND url like 'like_str'
AND sip not in (select ip from `dlz_exclude_ip`)
GROUP BY tm, uid
) AS tb GROUP BY tm ORDER BY hr ) AS lt ON (hd.hr=lt.hr)
'''


select_new_user = '''
SELECT hd.hr, lt.tm, lt.evname, lt.num ,lt.money FROM 
    ( SELECT '00' AS hr 
        UNION SELECT '01'
        UNION SELECT '02' 
        UNION SELECT '03' 
        UNION SELECT '04' 
        UNION SELECT '05' 
        UNION SELECT '06' 
        UNION SELECT '07' 
        UNION SELECT '08' 
        UNION SELECT '09' 
        UNION SELECT '10' 
        UNION SELECT '11' 
        UNION SELECT '12' 
        UNION SELECT '13' 
        UNION SELECT '14' 
        UNION SELECT '15' 
        UNION SELECT '16' 
        UNION SELECT '17' 
        UNION SELECT '18' 
        UNION SELECT '19' 
        UNION SELECT '20' 
        UNION SELECT '21' 
        UNION SELECT '22' 
        UNION SELECT '23') AS hd
				LEFT JOIN ( SELECT SUBSTRING(tm, 12, 2) AS hr, tm,evname, count(tl) AS 'num',sum(money) as 'money' FROM 
				( SELECT uid, SUBSTRING(DATE_ADD(`time`, INTERVAL 8 HOUR), 1, 13) AS tm, COUNT(*) AS tl,
				sum(SUBSTRING_INDEX(SUBSTRING_INDEX(content,",uemail:",1),'total:',-1)) as money, evname FROM `gather_record` 
WHERE evname in ('ev_name') AND `time`>= 'star_time' AND `time`< 'end_time' AND `host`='site_str'
AND sip not in (select ip from `dlz_exclude_ip`)
AND uid not in (select uid from gather_dlzuserinfo where `create_time`>= 'fifteen_day_ago' 
                    AND `create_time`< 'one_day_ago' AND `site` = 'site_str')
GROUP BY tm, uid
) AS tb GROUP BY tm ORDER BY hr ) AS lt ON (hd.hr=lt.hr)
'''

def get_data(start:datetime,end:datetime,num_days:int,site:str,country:str):
    result=[]
    types = ["load-page", "addto-cart", "checkout-create", "order-thanks"]
    for day in range(0,num_days):
        one_day_data={}
        next_star = start + timedelta(days=day) - timedelta(hours=8)
        next_end = start + timedelta(days=day + 1) - timedelta(hours=8)
        for tp in types:
            db_conn = get_mirror_conn()
            sql = f'''{select.replace('star_time', str(next_star)).replace('end_time', str(next_end)).replace('ev_name', tp).replace('site_str', site)};'''
            sql = sql_link_country(sql, country, site,tp)
            print(sql)
            r = db_conn.query_all(sql)
            one_day_data[tp]=r

        #查询结账次数
        tp2="checkout_out"
        db_conn_2 = get_mirror_conn()
        sql_2 = f'''
            {select_checkout.replace('star_time', str(next_star))
            .replace('end_time', str(next_end)).replace('ev_name', 'load-page')
            .replace('site_str', site)
            .replace("like_str",str('%%checkout%%'))};
                '''
        sql_2 = sql_link_country(sql_2, country, site,tp2)
        r2 = db_conn_2.query_all(sql_2)
        one_day_data[tp2] = r2

        # 查询新用户数
        fifteen_day_ago = start + timedelta(days=day - 15) - timedelta(hours=8)
        one_day_ago = start + timedelta(days=day - 1) - timedelta(hours=8)
        tp3 = "new_user"
        db_conn_3 = get_mirror_conn()
        sql_3 = f'''
                   {select_new_user.replace('star_time', str(next_star))
            .replace('end_time', str(next_end)).replace('ev_name', 'load-page')
            .replace('site_str', site)
            .replace("like_str", str('%%checkout%%'))
            .replace('fifteen_day_ago', str(fifteen_day_ago)).replace('one_day_ago', str(one_day_ago))};
                       '''
        sql_3 = sql_link_country(sql_3, country, site,tp3)
        print(sql_3)
        r3 = db_conn_3.query_all(sql_3)
        one_day_data[tp3] = r3


        for ind in range(0, 24):
            one_result = {}
            money = int(one_day_data['order-thanks'][ind]['money']) if one_day_data['order-thanks'][ind]['money'] else 0
            new_user = int(one_day_data['new_user'][ind]['num']) if one_day_data['new_user'][ind]['num'] else 0
            load_page = int( one_day_data['load-page'][ind]['num'] ) if one_day_data['load-page'][ind]['num'] else 0
            addto_cart = int(one_day_data['addto-cart'][ind]['num']) if one_day_data['addto-cart'][ind]['num'] else 0
            checkout_create = int(one_day_data['checkout-create'][ind]['num']) if one_day_data['checkout-create'][ind]['num'] else 0
            checkout_out = int(one_day_data['checkout_out'][ind]['num']) if one_day_data['checkout_out'][ind]['num'] else 0
            order_thanks = int(one_day_data['order-thanks'][ind]['num']) if one_day_data['order-thanks'][ind]['num'] else 0

            add_per = round((addto_cart / load_page), 4) if load_page else 000
            check_per = round((checkout_create / addto_cart), 4) if addto_cart else 000
            thanks_per = round((order_thanks / checkout_create), 4) if checkout_create else 000
            money_per = round((money / order_thanks), 4) if order_thanks else 000
            u2_checkout = round((checkout_out / load_page), 4) if load_page else 000
            cart2_c = round((checkout_out / addto_cart), 4) if addto_cart else 000
            u2_o = round((checkout_create / load_page), 4) if load_page else 000
            c2_o = round((checkout_create / checkout_out), 4) if checkout_out else 000
            o2_p = round((order_thanks / checkout_create), 4) if checkout_create else 000
            c2_p = round((order_thanks / checkout_out), 4) if checkout_out else 000

            # 日期
            one_result['datetime'] = str(one_day_data['load-page'][ind]['tm'])
            # 收入：当天订单产生的总金额
            one_result['money'] = money
            # ARPPU：收入 / 购买
            one_result['money_per'] = money_per
            # 购买：成功订单数（订单状态 = processing / shipped / refund）
            one_result['order_thanks'] = order_thanks
            # Order Number：生成的订单数
            one_result['checkout_create'] = checkout_create
            # 加车次数：唯一身份加车次数
            one_result['addto_cart'] = addto_cart
            # 结账次数：唯一身份进入checkout页面的次数 还需修改
            one_result['checkout_out'] = checkout_out
            # 新用户数
            one_result['new_num'] = new_user
            # 用户数    ：唯一身份的user
            one_result['load_page'] = load_page
            # U2Cart：加车次数 / 用户数
            one_result['add_per'] = to_format_str(add_per)
            # Cart2C：结账次数 / 加车次数
            one_result['cart2_c'] = to_format_str(cart2_c)
            # U2Checkout：结账次数 / 用户数
            one_result['u2_checkout'] = to_format_str(u2_checkout)
            # U2O：Order Number / 用户数
            one_result['u2_o'] = to_format_str(u2_o)
            # C2O：Order Number / 结账次数
            one_result['c2_o'] = to_format_str(c2_o)
            # o2p：购买 / Order umber
            one_result['o2_p'] = to_format_str(o2_p)
            # C2P：购买 / 结账次数
            one_result['c2_p'] = to_format_str(c2_p)
            # 国家
            one_result['country'] = country_selector(country)
            result.append(one_result)
    return result

def to_format_str(params_int):
    return format(params_int, '.2%')

def country_selector(value: str) -> str:
    """
    根据传入的 value（如 '/en'）返回对应的描述或处理逻辑，实现一对一枚举选择
    """
    # 以 value 为 key，label 或处理逻辑为 value，构建映射字典
    country_map = {
        '/all': '全量',
        '/en': '英文',
        '/fr': '法语',
        '/es': '西班牙语',
        '/ja': '日语',
        '/zh': '繁体中文',
        '/ar': '阿拉伯语',
        '/de': '德语',
        '/pt': '葡萄牙语',
    }
    # 返回匹配结果，若不存在则返回默认值（可自定义）
    return country_map.get(value, '未知语言')


def sql_link_country(sql, country, site, tp):
    # 基础的User-Agent过滤条件，排除各种爬虫和机器人
    base_ua_filter = "AND ua NOT REGEXP 'bot|crawl|spider|slurp|robot|curl|facebook|twitter|linkedin|google|baidu|bing|yandex|duckduckgo|Headless|meta-externalagent/1.1|meta-externalfetcher/1.1'"

    # 根据tp参数确定使用的字段
    field = "url" if tp == "addto-cart" else "page"

    # 定义非英语国家代码列表
    non_en_countries = ["fr", "es", "ja", "zh", "ar", "de", "pt"]

    # 根据country参数构建查询条件
    if country == "/all":
        sql_condition = base_ua_filter
    elif country == "/en":
        # 构建排除所有非英语国家代码的条件
        country_filters = [f"AND {field} NOT LIKE '%%/{code}%%'" for code in non_en_countries]
        sql_condition = "\n".join(country_filters + [base_ua_filter])
    else:
        # 构建特定国家代码的条件
        sql_condition = f"AND {field} LIKE '%%{country}%%' {base_ua_filter}"

    return add_condition_to_sql(sql, site, sql_condition)



def add_condition_to_sql(sql, host_var, new_condition):
    """
    在SQL查询的AND `host` = {host_var}条件后添加新条件

    参数:
    sql (str): 原始SQL查询
    host_var (str): 主机名变量（如'gentlebands.com'）
    new_condition (str): 要添加的新条件，如"url LIKE '%example%'"

    返回:
    str: 修改后的SQL查询
    """
    # 构建目标条件的正则模式（支持引号转义）
    target_pattern = f"AND `host`='{host_var}'"

    # 查找目标条件的位置
    pos = sql.find(target_pattern)
    print(pos)
    if pos == -1:
        raise ValueError(f"未找到目标条件: AND `host` = '{host_var}'")

    # 计算插入位置（目标条件之后）
    insert_pos = pos + len(target_pattern)

    # 检查目标条件后是否已有AND
    remaining_sql = sql[insert_pos:].lstrip()
    if remaining_sql.startswith("AND"):
        # 如果已有AND，直接添加新条件（用空格分隔）
        return f"{sql[:insert_pos]} {new_condition}{sql[insert_pos:]}"
    else:
        # 如果没有AND，添加AND和新条件
        return f"{sql[:insert_pos]} AND {new_condition}{sql[insert_pos:]}"

@require_http_methods(["GET"])
def site_statistics(request):
    global counts, select, limit, froms
    params = request.GET
    re = json.loads(json.dumps(def_response_o,ensure_ascii=False))
    start = params['start'] if 'start' in params.keys() else ''
    end = params['end'] if 'end' in params.keys() else ''
    site = params['site'] if 'site' in params.keys() else ''
    country = params['country'] if 'country' in params.keys() else ''
    if not start or not end or not site or not country:
        re['code'] = 404
        re['type'] = 'fail'
        re['message'] = "请指定起止日期,站点和国家"
        return JsonResponse(re)
    start_day=start.split(' ')[0]
    end_day=end.split(' ')[0]
    start = datetime.strptime(start_day, "%Y-%m-%d")
    end = datetime.strptime(end_day, "%Y-%m-%d")
    delta = end - start
    num_days = delta.days
    if num_days>15:
        re['code'] = 404
        re['type'] = 'fail'
        re['message'] = "时间间隔不能大于15天"
        return JsonResponse(re)
    result=get_data(start,end,num_days+1,site,country)
    re['result'] = result
    return JsonResponse(re)

@require_http_methods(["GET"])
def down_site_statistics_excel(request):
    global counts, select, limit, froms
    params = request.GET
    re = json.loads(json.dumps(def_response_o,ensure_ascii=False))
    start = params['start'] if 'start' in params.keys() else ''
    end = params['end'] if 'end' in params.keys() else ''
    site = params['site'] if 'site' in params.keys() else ''
    country = params['country'] if 'country' in params.keys() else ''
    if not start or not end or not site or not country:
        re['code'] = 404
        re['type'] = 'fail'
        re['message'] = "请指定起止日期,站点和国家"
        return JsonResponse(re)
    start_day=start.split(' ')[0]
    end_day=end.split(' ')[0]
    start = datetime.strptime(start_day, "%Y-%m-%d")
    end = datetime.strptime(end_day, "%Y-%m-%d")
    delta = end - start
    num_days = delta.days
    if num_days>15:
        re['code'] = 404
        re['type'] = 'fail'
        re['message'] = "时间间隔不能大于15天"
        return JsonResponse(re)
    result=get_data(start,end,num_days+1,site,country)

    start=str(start).split(" ")[0]
    end=str(end).split(" ")[0]
    title_name = f'{start}-to-{end}'
        # 创建一个Excel工作簿
    excel_file = Workbook()
    sheet = excel_file.active
    sheet.title = title_name

    sheet[f'A1'] = "日期"
    sheet[f'B1'] = "收入"
    sheet[f'C1'] = "ARPPU"
    sheet[f'D1'] = "购买"
    sheet[f'E1'] = "Order Number"
    sheet[f'F1'] = "加车次数"
    sheet[f'G1'] = "结账次数"
    sheet[f'H1'] = "新用户数"
    sheet[f'I1'] = "用户数"
    sheet[f'J1'] = "U2Cart"
    sheet[f'K1'] = "Cart2C"
    sheet[f'L1'] = "U2Checkout"
    sheet[f'M1'] = "U2O"
    sheet[f'N1'] = "C2O"
    sheet[f'O1'] = "o2p"
    sheet[f'P1'] = "C2P"
    sheet[f'Q1'] = "国家"

    for i in range(len(result)):
        no = i+1
        # 添加一些数据
        sheet[f'A{no+1}'] = result[i]['datetime']
        sheet[f'B{no+1}'] = result[i]['money']
        sheet[f'C{no+1}'] = result[i]['money_per']
        sheet[f'D{no+1}'] = result[i]['order_thanks']
        sheet[f'E{no+1}'] = result[i]['checkout_create']
        sheet[f'F{no+1}'] = result[i]['addto_cart']
        sheet[f'G{no+1}'] = result[i]['checkout_out']
        sheet[f'H{no+1}'] = result[i]['new_num']
        sheet[f'I{no+1}'] = result[i]['load_page']
        sheet[f'J{no + 1}'] = result[i]['add_per']
        sheet[f'K{no + 1}'] = result[i]['cart2_c']
        sheet[f'L{no + 1}'] = result[i]['u2_checkout']
        sheet[f'M{no + 1}'] = result[i]['u2_o']
        sheet[f'N{no + 1}'] = result[i]['c2_o']
        sheet[f'O{no + 1}'] = result[i]['o2_p']
        sheet[f'P{no + 1}'] = result[i]['c2_p']
        sheet[f'Q{no + 1}'] = result[i]['country']
    
    # 保存工作簿到内存中的BytesIO
    excel_bytes_io = BytesIO()
    excel_file.save(excel_bytes_io)
    excel_bytes_io.seek(0)
    
    response = HttpResponse(content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
    response['Content-Disposition'] = f'attachment; filename={title_name}'
    response.write(excel_bytes_io.getvalue())
    return response